package com.fashionbrot.github.util;

import com.alibaba.fastjson.JSON;
import com.fashionbrot.github.exception.MarsException;
import com.github.fashionbrot.tool.ObjectUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;

import java.math.BigDecimal;
import java.util.Date;
import java.util.LinkedHashMap;

@Slf4j
public class ExcelUtil {


    private static String letter = "abcdefghijklmnopqrstuvwxyz";
    private static LinkedHashMap<String, Integer> row = new LinkedHashMap<String, Integer>();

    private static LinkedHashMap<Integer, String> numberColumnMap = new LinkedHashMap<Integer, String>();

    static {
        char[] chars = letter.toCharArray();
        for (int i = 0; i < chars.length; i++) {
            char c = chars[i];
            row.put("" + c, i);
            numberColumnMap.put(i, c + "");
            if (i == 25) {
                int count = 25;
                for (int j = 0; j < chars.length; j++) {
                    count++;
                    char cc = chars[j];
                    row.put("a" + cc, count);
                    numberColumnMap.put(count, "a" + cc);
                }

                int count2 = 51;
                for (int k = 0; k < chars.length; k++) {
                    count2++;
                    char cc = chars[k];
                    row.put("b" + cc, count2);
                    numberColumnMap.put(count2, "b" + cc);
                }

                int count3 = 77;
                for (int p = 0; p < chars.length; p++) {
                    count3++;
                    char cc = chars[p];
                    row.put("c" + cc, count3);
                    numberColumnMap.put(count3, "c" + cc);
                }
            }
        }
        log.info(JSON.toJSONString(row));
    }

    public static Integer getRowColumn(String excelColumn) {
        String column = excelColumn.toLowerCase();
        if (row.containsKey(column)) {
            return row.get(column);
        } else {
            throw new MarsException("获取excel 配置信息错误1 excelColumn:" + excelColumn);
        }
    }

    public static String getRowColumn(Integer number) {
        if (numberColumnMap.containsKey(number)) {
            return numberColumnMap.get(number);
        } else {
            throw new MarsException("获取excel 配置信息错误2 number:" + number);
        }
    }

    public static void main(String[] args) {
        System.out.println(getRowColumn("aj"));
        System.out.println(getRowColumn("c"));
    }


    public static void createCell(HSSFSheet sheet, int rowNum, int cellNum, String value, CellStyle cellStyle){
        HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        HSSFCell cell = row.createCell(cellNum);
        cell.setCellValue(value);
        if (cellStyle!=null) {
            cell.setCellStyle(cellStyle);
        }
    }

    public static void createCell(HSSFSheet sheet, int rowNum, int cellNum, CellStyle cellStyle,String formula){
        HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        HSSFCell cell = row.createCell(cellNum);
        if (cellStyle!=null) {
            cell.setCellStyle(cellStyle);
        }
        if (StringUtil.isNotEmpty(formula)) {
            cell.setCellFormula(formula);
        }
    }

    public static void createCell(Sheet sheet, int rowNum, int cellNum, CellStyle cellStyle,String formula){
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        Cell cell = row.createCell(cellNum);
        if (cellStyle!=null) {
            cell.setCellStyle(cellStyle);
        }
        if (StringUtil.isNotEmpty(formula)) {
            cell.setCellFormula(formula);
        }
    }

    public static void createCellValue(HSSFSheet sheet, int rowNum, int cellNum, Object value, CellStyle cellStyle){
        HSSFRow row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        HSSFCell cell = row.createCell(cellNum);

        if (value instanceof Double){
            cell.setCellValue(ObjectUtil.formatDouble(value));
        }else if (value instanceof BigDecimal){
            BigDecimal bigDecimal= (BigDecimal) value;
            cell.setCellValue(bigDecimal!=null?bigDecimal.doubleValue():0.00D);
        }else if (value instanceof String){
            cell.setCellValue((String)value);
        }else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        }else if (value instanceof Date){
            cell.setCellValue((Date) value);
        }

        if (cellStyle!=null) {
            cell.setCellStyle(cellStyle);
        }

    }

    public static void createCellValue(Sheet sheet, int rowNum, int cellNum, Object value, CellStyle cellStyle){
        Row row = sheet.getRow(rowNum);
        if (row == null) {
            row = sheet.createRow(rowNum);
        }
        Cell cell = row.createCell(cellNum);

        if (value instanceof Double){
            cell.setCellValue(ObjectUtil.formatDouble(value));
        }else if (value instanceof BigDecimal){
            BigDecimal bigDecimal= (BigDecimal) value;
            cell.setCellValue(bigDecimal!=null?bigDecimal.doubleValue():0.00D);
        }else if (value instanceof String){
            cell.setCellValue((String)value);
        }else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        }else if (value instanceof Date){
            cell.setCellValue((Date) value);
        }

        if (cellStyle!=null) {
            cell.setCellStyle(cellStyle);
        }

    }

    public static Cell createCell(Sheet sheet,int rowIndex, int cellNum, Object value, CellStyle cellStyle){
        Row row = sheet.getRow(rowIndex);
        if (row==null){
            row = sheet.createRow(rowIndex);
        }
        Cell cell = row.getCell(cellNum);
        if (cell==null){
            cell  = row.createCell(cellNum);
        }
        if (cellStyle!=null){
            cell.setCellStyle(cellStyle);
        }
        setCellValue(cell,value);
        return cell;
    }

    public static void createCell(Row row, int cellNum, Object value, CellStyle cellStyle){
        Cell cell = row.getCell(cellNum);
        if (cell==null){
            cell  = row.createCell(cellNum);
        }
        if (cellStyle!=null){
            cell.setCellStyle(cellStyle);
        }
        setCellValue(cell,value);
    }


    public static void createCell(Row row, int cellNum, Object value, String cellFormula){
        Cell cell = row.getCell(cellNum);
        if (cell==null){
            cell  = row.createCell(cellNum);
        }
        if (StringUtil.isNotEmpty(cellFormula)){
            cell.setCellFormula(cellFormula);
        }
        setCellValue(cell,value);
    }

    public static void createCell(Row row, int cellNum, Object value, String cellFormula, CellStyle cellStyle){
        Cell cell = row.getCell(cellNum);
        if (cell==null){
            cell  = row.createCell(cellNum);
        }
        if (StringUtil.isNotEmpty(cellFormula)){
            cell.setCellFormula(cellFormula);
        }
        if (cellStyle!=null){
            cell.setCellStyle(cellStyle);
        }
        setCellValue(cell,value);
    }

    public static void createCell(Row row, int cellNum, Object value){
        Cell cell = row.getCell(cellNum);
        if (cell==null){
            cell  = row.createCell(cellNum);
        }
        setCellValue(cell,value);
    }

    public static void setCellValue(Cell cell,Object value){
        if (value instanceof Double){
            cell.setCellValue(BigDecimalUtil.formatDouble(value,2));
        }else if (value instanceof BigDecimal){
            BigDecimal bigDecimal= (BigDecimal) value;
            cell.setCellValue( BigDecimalUtil.formatBigDecimal(bigDecimal!=null?bigDecimal.doubleValue():0.00D,2).doubleValue());
        }else if (value instanceof String){
            cell.setCellValue((String)value);
        }else if (value instanceof Long) {
            cell.setCellValue((Long) value);
        }else if (value instanceof Date){
            cell.setCellValue((Date) value);
        }else if (value instanceof Integer){
            cell.setCellValue( value+"");
        }else{
            if (value!=null){
                log.error("setCellValue error"+value.getClass());
            }
            throw new MarsException("未找到类型");
        }
    }

    public static Object getCellValue(Cell cell){
        if (cell!=null){
            if (cell.getCellTypeEnum() == CellType.NUMERIC){
                return cell.getNumericCellValue();
            }else if (cell.getCellTypeEnum() == CellType.STRING) {
                return cell.getStringCellValue();
            }else if (cell.getCellTypeEnum() == CellType.FORMULA){
                return cell.getNumericCellValue();
            }else{
                if (cell!=null){
                    log.error("getCellValue error"+cell.getCellTypeEnum().name());
                }
                throw new MarsException("未找到类型");
            }
        }
        return null;
    }


    public static CellStyle setStyle(Workbook workBook){
        return setStyle(workBook,IndexedColors.GREEN);
    }


    public static CellStyle setStyle(Workbook workBook,IndexedColors colors){
        CellStyle cellStyle=workBook.createCellStyle();
//        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        cellStyle.setDataFormat(workBook.createDataFormat().getFormat("0.00"));
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(colors.getIndex());//背景颜色

        cellStyle.setBorderBottom(BorderStyle.MEDIUM); //下边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderLeft(BorderStyle.MEDIUM);//左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderTop(BorderStyle.MEDIUM);//上边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderRight(BorderStyle.MEDIUM);//右边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
        cellStyle.setShrinkToFit(true);

        return cellStyle;
    }


    public static CellStyle setStyle(Workbook workBook,IndexedColors colors,String format){
        CellStyle cellStyle=workBook.createCellStyle();
        if (format!=null){
            cellStyle.setDataFormat(workBook.createDataFormat().getFormat(format));
        }
        cellStyle.setAlignment(HorizontalAlignment.CENTER);

        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(colors.getIndex());//背景颜色

        cellStyle.setBorderBottom(BorderStyle.MEDIUM); //下边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderLeft(BorderStyle.MEDIUM);//左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderTop(BorderStyle.MEDIUM);//上边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderRight(BorderStyle.MEDIUM);//右边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
        cellStyle.setShrinkToFit(true);
        return cellStyle;
    }

    public static CellStyle getStyle(Workbook wb,String format,boolean shrinkToFit){
        CellStyle cellStyle=wb.createCellStyle();
        if (StringUtil.isNotEmpty(format)){
            cellStyle.setDataFormat(wb.createDataFormat().getFormat(format));
        }

        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        cellStyle.setBorderBottom(BorderStyle.MEDIUM); //下边框
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderLeft(BorderStyle.MEDIUM);//左边框
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderTop(BorderStyle.MEDIUM);//上边框
        cellStyle.setTopBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderRight(BorderStyle.MEDIUM);//右边框
        cellStyle.setRightBorderColor(IndexedColors.BLACK.index);
        cellStyle.setShrinkToFit(shrinkToFit);
        return cellStyle;
    }


    /**
     * 创建标题样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
//        cellStyle.setFillBackgroundColor(IndexedColors.GREY_40_PERCENT.getIndex());

        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        HSSFFont headerFont1 = (HSSFFont) wb.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("黑体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式

        return cellStyle;
    }

    /**
     * 创建标题样式
     * @param wb
     * @return
     */
    public static CellStyle createTitleCellStyle(Workbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
//        cellStyle.setFillBackgroundColor(IndexedColors.GREY_40_PERCENT.getIndex());

        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        Font headerFont1 =  wb.createFont(); // 创建字体样式
        headerFont1.setBold(true); //字体加粗
        headerFont1.setFontName("黑体"); // 设置字体类型
        headerFont1.setFontHeightInPoints((short) 15); // 设置字体大小
        cellStyle.setFont(headerFont1); // 为标题样式设置字体样式

        return cellStyle;
    }


    /**
     * 创建表头样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框


        HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 24); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }

    /**
     * 创建表头样式
     * @param wb
     * @return
     */
    public static CellStyle createHeadCellStyle(Workbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());//背景颜色
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //水平居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //垂直对齐
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        Font headerFont =  wb.createFont(); // 创建字体样式
        headerFont.setBold(true); //字体加粗
        headerFont.setFontName("黑体"); // 设置字体类型
        headerFont.setFontHeightInPoints((short) 24); // 设置字体大小
        cellStyle.setFont(headerFont); // 为标题样式设置字体样式

        return cellStyle;
    }

    /**
     * 创建内容样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        // 生成12号字体
        HSSFFont font = wb.createFont();
        font.setColor((short)8);
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);

        return cellStyle;
    }

    /**
     * 创建内容样式
     * @param wb
     * @return
     */
    public static CellStyle createContentCellStyle(Workbook wb) {
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);// 水平居中
        cellStyle.setWrapText(true);// 设置自动换行
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框

        // 生成12号字体
        Font font = wb.createFont();
        font.setColor((short)8);
        font.setFontHeightInPoints((short) 12);
        cellStyle.setFont(font);

        return cellStyle;
    }


    public static void setColumnWidth(Sheet sheet, int cellNum) {
        sheet.autoSizeColumn(cellNum );
        sheet.setColumnWidth(cellNum , Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(cellNum ) * 11 / 10)));
    }

    public static void setSXSSFWorkbookColumnWidth(Sheet sheet, int cellNum) {
//        sheet.autoSizeColumn(cellNum );
        sheet.setColumnWidth(cellNum , Math.max(15 * 256, Math.min(255 * 256, sheet.getColumnWidth(cellNum ) * 11 / 10)));
//        sheet.setColumnWidth(cellNum,sheet.getColumnWidth(0) *19 /10);
    }



    public static void setColumnWidth(Sheet sheet, int cellNum,int fontCount) {
        sheet.autoSizeColumn(cellNum );
        sheet.setColumnWidth(cellNum , (sheet.getColumnWidth(cellNum )/256+3) * 256);
    }

    /**
     *
     * @param sheet
     * @param rowNum
     * @param cellNum
     * @param eliminate 要剔除计算长度的字符串
     */
    public static void setColumnWidth(Sheet sheet,int rowNum,int cellNum,String eliminate){

        Row row = sheet.getRow(rowNum);
        for(int i=0;i<=cellNum;i++){

            if (sheet instanceof SXSSFSheet){

            }else{
                sheet.autoSizeColumn(i );
            }
            int columnWidth = sheet.getColumnWidth(i) / 256;
            int length = 0;
            if (com.github.fashionbrot.tool.StringUtil.isNotEmpty(eliminate)){
                length = row.getCell(i).getStringCellValue().replaceAll(eliminate,"").getBytes().length;
            }else{
                length = row.getCell(i).getStringCellValue().getBytes().length;
            }
            if (columnWidth < length) {
                columnWidth = length;
            }
            sheet.setColumnWidth(i, columnWidth * 256);
        }
    }


}
